package com.eling.elcms.travel.dao.hibernate;

import com.eling.elcms.core.dao.hibernate.GenericDaoHibernate;
import com.eling.elcms.travel.dao.ICertificatesTransferDao;
import com.eling.elcms.travel.model.CertificatesTransfer;
import com.eling.elcms.travel.model.view.CertificateTransferView;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class CertificatesTransferDaoHibernate extends GenericDaoHibernate<CertificatesTransfer, Long> implements ICertificatesTransferDao {
	public CertificatesTransferDaoHibernate() {
		super(CertificatesTransfer.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<CertificateTransferView> queryNotes(Date operateDate, Date operateDateEnd) {
		StringBuffer sql = new StringBuffer();
		sql.append("  SELECT  ");
		sql.append("  tc.contactNo, tc.memberCertificates, tc.number, tc.balance,  ");
		sql.append("  tc.signUpDate ,wf.balance AS wfBalance, pro.name AS proName,pt.name AS ptName, cf.assignor, ");
		sql.append("  mem2. NAME AS residenceName ,cf.operateDate ,cf.transferType , cf.remark , cf.operator,cf.pkCertificatesTransfer  ");
		sql.append("  FROM  ");
		sql.append("  travel_certificatestransfer cf  ");
		sql.append("   INNER JOIN travel_travelcontract tc ON cf.pkTravelContract = tc.pkTravelContract ");
		sql.append("   INNER JOIN travel_productrule pr ON pr.pkProduct = tc.pkProductRule ");
		sql.append("   INNER JOIN travel_product pro ON pro.pkProduct = pr.pkProduct ");
		sql.append("   INNER JOIN travel_producttype pt ON pt.pkProductType = tc.pkProductType ");
		sql.append("   INNER JOIN travel_welfare wf ON wf.pkTravelContract = tc.pkTravelContract ");
		sql.append("  INNER JOIN mem_member mem2 ON mem2.pkMember = cf.receiver  ");
		sql.append("   WHERE ");
		sql.append("   cf.operateDate >=:operateDate AND cf.operateDate <=:operateDateEnd ");
		sql.append("   GROUP BY tc.contactNo ");
		/*sql.append("    ");
		sql.append("    ");
		sql.append("    ");
		sql.append("    ");
		sql.append("    ");
		sql.append("    ");*/
		List<Object[]> objList = getSession().createNativeQuery(sql.toString())
				.setParameter("operateDate", operateDate)
				.setParameter("operateDateEnd", operateDateEnd).list();
		
		List<CertificateTransferView> cerList = new ArrayList<>();
		
		for(int i=0; i<objList.size();i++){
			CertificateTransferView cer = new CertificateTransferView();
			Object[] objects = objList.get(i);
			cer.setContactNo(objects[0]==null? null :objects[0].toString());
			cer.setMemberCertificates(objects[1]==null? null :objects[1].toString());
			cer.setNumber( objects[2]==null? 0 :new Integer(objects[2].toString()));
			cer.setBalance(objects[3]==null? 0.00 :new Double(objects[3].toString()));
			cer.setSignDate(objects[4]==null? null :(Date)objects[4]);
			cer.setWelfarebalance(objects[5]==null? 0.00 :new Double(objects[5].toString()));
			cer.setProductName(objects[6]==null? null :objects[6].toString());
			cer.setProductType(objects[7]==null? null : objects[7].toString());
			cer.setAssignor(objects[8]==null? null :objects[8].toString());
			cer.setReceiver(objects[9]==null? null:objects[9].toString());
			cer.setOperateDate(objects[10]==null? null :(Date)objects[10]);
			cer.setTransferType(objects[11]==null? null :objects[11].toString());
			cer.setRemark(objects[12]==null? null : objects[12].toString());
			cer.setOperater(objects[13]==null? null :objects[13].toString());
			cer.setPkCertificatesTransfer(new Long(objects[14].toString()));
			cerList.add(cer);
		}
		return cerList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<CertificateTransferView> queryNotess(Date operateDate, Date operateDateEnd,
			Long pkCertificatesTransfer) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append("  cf.operateDate,mem.name AS assignorName,mem2. NAME AS residenceName,cf.transferType,tc.balance AS tcbalance, wf.balance AS wfbalance,cf.remark,mem3.name AS operaterName");
		sql.append(" FROM ");
		sql.append(" `travel_certificatestransfer` cf  ");
		sql.append("  INNER JOIN travel_travelcontract tc ON cf.pkTravelContract = tc.pkTravelContract  ");
		sql.append("  INNER JOIN travel_welfare wf ON wf.pkTravelContract = tc.pkTravelContract  ");
		sql.append("  INNER JOIN travel_customer cus ON cus.pkCustomer = cf.operator  ");
		sql.append("  INNER JOIN mem_member mem ON mem.pkMember = cf.assignor  ");
		sql.append("  INNER JOIN mem_member mem2 ON mem2.pkMember = cf.receiver  ");
		sql.append("  INNER JOIN mem_member mem3 ON mem3.pkMember = cus.pkCustomer  ");
		sql.append("    ");
		sql.append("  where cf.pkCertificatesTransfer = "+pkCertificatesTransfer+"");
		sql.append("  AND  ");
		sql.append("  cf.operateDate >=:operateDate AND cf.operateDate <=:operateDateEnd  ");
		sql.append("  ");
		
		List<Object[]> objList = getSession().createNativeQuery(sql.toString())
				.setParameter("operateDate", operateDate)
				.setParameter("operateDateEnd", operateDateEnd).list();
		List<CertificateTransferView> cerlist = new ArrayList<>();
		for(int i = 0; i<objList.size();i++){
			CertificateTransferView cer = new CertificateTransferView();
			Object[] objects = objList.get(i);
			cer.setOperateDate(objects[0]==null? null : (Date)objects[0]);
			cer.setAssignor(objects[1]==null? null:objects[1].toString());
			cer.setReceiver(objects[2]==null? null:objects[2].toString());
			cer.setTransferType(objects[3] == null? null:objects[3].toString());
			cer.setBalance(objects[4]==null?0.00:new Double (objects[4].toString()));
			cer.setWelfarebalance(objects[5] ==null ? 0.00 : new Double(objects[5].toString()));
			cer.setRemark(objects[6] == null ? null : objects[6].toString());
			cer.setOperater(objects[7] ==null ? null : objects[7].toString());
			cerlist.add(cer);
		}
		
		return cerlist;
	}
}
